﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using StudentsManager.Models;
using StudentsManager.Exceptions;
using System.Data.SQLite;

namespace StudentsManager.DataAccess
{
    public class StudentDataAccess : DatabaseHelper
    {
        // 添加学生
        public void AddStudent(Student student)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();

                string query = "SELECT Id FROM Schools WHERE Name = @schoolName";
                var schoolCommand = new SQLiteCommand(query, connection);
                schoolCommand.Parameters.AddWithValue("@schoolName", student.SchoolName);
                int schoolId = Convert.ToInt32(schoolCommand.ExecuteScalar());
                if (schoolId == 0)
                {
                    throw new NotExistException("学校不存在。");
                }

                query = "SELECT Id FROM Classes WHERE Name = @className AND SchoolId = @schoolId";
                var classCommand = new SQLiteCommand(query, connection);
                classCommand.Parameters.AddWithValue("@className", student.ClassName);
                classCommand.Parameters.AddWithValue("@schoolId", schoolId);
                int classId = Convert.ToInt32(classCommand.ExecuteScalar());
                if (classId == 0)
                {
                    throw new NotExistException("班级不存在。");
                }

                var command = new SQLiteCommand("INSERT INTO Students (Name, Age, gender, ClassId) VALUES (@name, @age, @gender, @classId)", connection);
                command.Parameters.AddWithValue("@name", student.Name);
                command.Parameters.AddWithValue("@age", student.Age);
                command.Parameters.AddWithValue("@gender", student.Gender);
                command.Parameters.AddWithValue("@classId", classId);
                command.ExecuteNonQuery();
            }
        }

        // 更新学生
        public void UpdateStudent(Student student)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();

                string query = "SELECT Id FROM Schools WHERE Name = @schoolName";
                var schoolCommand = new SQLiteCommand(query, connection);
                schoolCommand.Parameters.AddWithValue("@schoolName", student.SchoolName);
                int schoolId = Convert.ToInt32(schoolCommand.ExecuteScalar());
                if (schoolId == 0)
                {
                    throw new NotExistException("学校不存在。");
                }

                query = "SELECT Id FROM Classes WHERE Name = @className AND SchoolId = @schoolId";
                var classCommand = new SQLiteCommand(query, connection);
                classCommand.Parameters.AddWithValue("@className", student.ClassName);
                classCommand.Parameters.AddWithValue("@schoolId", schoolId);
                int classId = Convert.ToInt32(classCommand.ExecuteScalar());
                if (classId == 0)
                {
                    throw new NotExistException("班级不存在。");
                }

                var command = new SQLiteCommand("UPDATE Students SET Name = @name, Age = @age, Gender = @gender, ClassId = @classId WHERE Id = @id", connection);
                command.Parameters.AddWithValue("@name", student.Name);
                command.Parameters.AddWithValue("@age", student.Age);
                command.Parameters.AddWithValue("@gender", student.Gender);
                command.Parameters.AddWithValue("@classId", classId);
                command.Parameters.AddWithValue("@id", student.Id);
                command.ExecuteNonQuery();
            }
        }

        // 删除学生
        public void DeleteStudent(int studentId)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();

                var command = new SQLiteCommand("DELETE FROM Students WHERE Id = @id", connection);
                command.Parameters.AddWithValue("@id", studentId);
                command.ExecuteNonQuery();
            }
        }

        // 查询所有学生
        public List<Student> GetStudentList()
        {
            return GetStudentList(null);
        }

        // 查询学生（根据classId）
        public List<Student> GetStudentList(int classId)
        {
            return GetStudentList(new Student { ClassId = classId });
        }

        // 查询学生（根据student中的信息）
        public List<Student> GetStudentList(Student student)
        {
            List<Student> res = new List<Student>();

            // 使用SQLite连接
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();

                // 如果传入的学生对象为空，则查询所有学生
                if (student == null)
                {
                    string query = "SELECT * FROM Students";
                    using (var command = new SQLiteCommand(query, connection))
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            res.Add(new Student
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString(),
                                Age = Convert.ToInt32(reader["Age"]),
                                Gender = Convert.ToInt32(reader["Gender"]),
                                ClassId = Convert.ToInt32(reader["ClassId"])
                            });
                        }
                    }
                    return res;
                }

                // 如果传入的学生对象只有ClassId有效，则查询指定班级的学生
                if (student.ClassId != 0)
                {
                    string query = "SELECT * FROM Students WHERE ClassId = @classId";
                    using (var command = new SQLiteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@classId", student.ClassId);
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                res.Add(new Student
                                {
                                    Id = Convert.ToInt32(reader["Id"]),
                                    Name = reader["Name"].ToString(),
                                    Age = Convert.ToInt32(reader["Age"]),
                                    Gender = Convert.ToInt32(reader["Gender"]),
                                    ClassId = Convert.ToInt32(reader["ClassId"])
                                });
                            }
                        }
                    }
                    return res;
                }

                // 创建基本的查询语句
                string baseQuery = "SELECT * FROM Students WHERE 1=1"; // 1=1是为了方便后续的条件拼接
                List<string> conditions = new List<string>();
                List<SQLiteParameter> parameters = new List<SQLiteParameter>();

                // 根据student的属性构建查询条件
                if (!string.IsNullOrEmpty(student.Name))
                {
                    conditions.Add("Name LIKE @name");
                    parameters.Add(new SQLiteParameter("@name", "%" + student.Name + "%")); // 使用LIKE进行模糊查询
                }

                if (student.Age > 0) // 假设年龄0表示不查询
                {
                    conditions.Add("Age = @age");
                    parameters.Add(new SQLiteParameter("@age", student.Age));
                }

                if (student.Gender >= 0) // 假设-1表示不查询
                {
                    conditions.Add("Gender = @gender");
                    parameters.Add(new SQLiteParameter("@gender", student.Gender));
                }

                if (!string.IsNullOrEmpty(student.ClassName))
                {
                    // 查询班级Id
                    string classQuery = "SELECT Id FROM Classes WHERE Name = @className";
                    int classId = -1;
                    using (var classCommand = new SQLiteCommand(classQuery, connection))
                    {
                        classCommand.Parameters.AddWithValue("@className", student.ClassName);
                        object result = classCommand.ExecuteScalar();
                        if (result != null)
                        {
                            classId = Convert.ToInt32(result);
                        }
                    }

                    if (classId != -1) // 如果找到有效的班级Id
                    {
                        conditions.Add("ClassId = @classId");
                        parameters.Add(new SQLiteParameter("@classId", classId));
                    }
                }

                if (!string.IsNullOrEmpty(student.SchoolName))
                {
                    // 查询学校Id
                    string schoolQuery = "SELECT Id FROM Schools WHERE Name = @schoolName";
                    int schoolId = -1;
                    using (var schoolCommand = new SQLiteCommand(schoolQuery, connection))
                    {
                        schoolCommand.Parameters.AddWithValue("@schoolName", student.SchoolName);
                        object result = schoolCommand.ExecuteScalar();
                        if (result != null)
                        {
                            schoolId = Convert.ToInt32(result);
                        }
                    }

                    // 如果学校Id有效，查询班级Id
                    if (schoolId != -1)
                    {
                        string classQuery = "SELECT Id FROM Classes WHERE SchoolId = @schoolId";
                        using (var classCommand = new SQLiteCommand(classQuery, connection))
                        {
                            classCommand.Parameters.AddWithValue("@schoolId", schoolId);
                            using (var classReader = classCommand.ExecuteReader())
                            {
                                List<int> classIds = new List<int>();
                                while (classReader.Read())
                                {
                                    classIds.Add(classReader.GetInt32(0));
                                }

                                if (classIds.Count > 0)
                                {
                                    conditions.Add("ClassId IN (" + string.Join(",", classIds) + ")");
                                }
                            }
                        }
                    }
                }

                // 将条件拼接到查询语句中
                if (conditions.Count > 0)
                {
                    baseQuery += " AND " + string.Join(" AND ", conditions);
                }

                // 执行查询
                using (var command = new SQLiteCommand(baseQuery, connection))
                {
                    command.Parameters.AddRange(parameters.ToArray());
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            res.Add(new Student
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString(),
                                Age = Convert.ToInt32(reader["Age"]),
                                Gender = Convert.ToInt32(reader["Gender"]),
                                ClassId = Convert.ToInt32(reader["ClassId"])
                            });
                        }
                    }
                }
            }

            return res;
        }

    }
}
